-- space used in the file system
set nocount on
exec ('select getdate() as [' + @@servername + ']')
select cast(instance_name as varchar(30)), cast(counter_name as varchar(30)), cntr_value
from master.dbo.sysperfinfo
where object_name = 'SQLServer:Databases'
and counter_name in (
'Data File(s) Size (KB)',
'Log File(s) Size (KB)',
'Log File(s) Used Size (KB)')
order by instance_name, counter_name

-- as falls away from 100% more memory can help
select cast(p2.cntr_value as decimal)/cast(p1.cntr_value as decimal)
from master.dbo.sysperfinfo p1
inner join master.dbo.sysperfinfo p2
on p1.object_name = p2.object_name
and p1.instance_name = p2.instance_name
where p1.object_name = 'SQLServer:Buffer Manager'
and p1.instance_name = ''
and p1.counter_name = 'Buffer cache hit ratio'
and p2.counter_name = 'Buffer cache hit ratio base'  

-- if buffer cache hit ratio getting smaller and scans increasing, new queries not using indexes
select cntr_value from master..sysperfinfo
where object_name = 'SQLServer:Access Methods'
and instance_name = ''
and counter_name = 'Full Scans/sec'

-- cache hit ratio by type 
select p1.instance_name, avg(cast(cast(p1.cntr_value as numeric(14,4))/cast(p2.cntr_value as numeric(14,4)) as numeric(6,4)) * 100)
from master.dbo.sysperfinfo p1
inner join master.dbo.sysperfinfo p2
on p1.object_name = p2.object_name
and p1.instance_name = p2.instance_name
where p1.object_name = 'SQLServer:Cache Manager'
and p1.counter_name = 'Cache Hit Ratio'
and p2.counter_name = 'Cache Hit Ratio Base'  
and p2.cntr_value <> 0 -- no div by 0
group by p1.instance_name